--*****DECLARE AND INITIALIZE DATA IN THE MASTER DETAIL TABLES

DECLARE @OMaster TABLE (ID INT PRIMARY KEY IDENTITY(1,1), Letter CHAR(1))
INSERT INTO @OMaster (Letter) VALUES ('A'),('B')

DECLARE @ODetail TABLE (ID INT PRIMARY KEY IDENTITY(1,1), MasterID INT, Letter CHAR(12))
INSERT INTO @ODetail (MasterID,Letter) VALUES (1,'A1'),(1,'A2'),(2,'B1'),(2,'B2')

SELECT 'ORIGINAL MASTER' AS TableName, * FROM @OMaster
SELECT 'ORIGINAL DETAIL' AS TableName, * FROM @ODetail

--*****DECLARE NEW MASTER DETAIL TABLES
DECLARE @NMaster TABLE (ID INT PRIMARY KEY IDENTITY(200,1), Letter CHAR(1))
DECLARE @NDetail TABLE (ID INT PRIMARY KEY IDENTITY(1,1), MasterID INT, Letter CHAR(12))
DECLARE @Correlation TABLE (OldMasterID INT,NewMasterID INT)

--MERGE THE MASTER DATA AND OUTPUT THE NEW MasterID to a CORRELATION TABLE
MERGE   INTO @NMaster AS T
USING   @OMaster AS S
ON	   (1=2)
WHEN	   NOT MATCHED THEN
	   INSERT (Letter) VALUES (S.Letter)
OUTPUT  S.ID, INSERTED.ID INTO @Correlation;

SELECT * FROM @Correlation


--MERGE THE DETAIL DATA USING THE NEW MasterID's and the Correlated information
MERGE   INTO @NDetail AS T
USING   (
		  SELECT	*
		  FROM	@ODetail AS O
				INNER JOIN @Correlation AS C ON (O.MasterID = C.OldMasterID)
	   ) AS S
ON	   (1=2)
WHEN	   NOT MATCHED THEN
	   INSERT (MasterID, Letter) VALUES (S.NewMasterID, S.Letter);
	   

SELECT 'NEW MASTER' AS TableName, * FROM @NMaster
SELECT 'NEW DETAIL' AS TableName, * FROM @NDetail